﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WebTemplate.DAL.Entities;
using System.Data.SqlClient;
using System.Configuration;

namespace WebTemplate.DAL.Repositories
{
    public class NewsRepository
    //: Connection<News>
    {

        public void Insert(News item)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "[News_Insert]";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@Title", item.Title);
            command.Parameters.AddWithValue("@ShortDescription", item.ShortDescription);
            command.Parameters.AddWithValue("@Description", item.Description);
            command.Parameters.AddWithValue("@Date", item.Date);
            command.Parameters.AddWithValue("@ImageUrl", item.ImageUrl ?? (object) DBNull.Value );
            command.Parameters.AddWithValue("@CreationDate", item.CreationDate);
            command.Parameters.AddWithValue("@CreatedBy", item.CreatedBy);
            command.Parameters.AddWithValue("@UpdateDate", item.UpdateDate ?? (object) DBNull.Value);
            command.Parameters.AddWithValue("@UpdateBy", item.UpdateBy ?? (object)DBNull.Value);
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public void Update(News item)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "[News_Update]";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@Id", item.Id);
            command.Parameters.AddWithValue("@Title", item.Title);
            command.Parameters.AddWithValue("@ShortDescription", item.ShortDescription);
            command.Parameters.AddWithValue("@Description", item.Description);
            command.Parameters.AddWithValue("@Date", item.Date);
            command.Parameters.AddWithValue("@ImageUrl", item.ImageUrl);
            command.Parameters.AddWithValue("@CreationDate", item.CreationDate);
            command.Parameters.AddWithValue("@CreatedBy", item.CreatedBy);
            command.Parameters.AddWithValue("@UpdateDate", item.UpdateDate);
            command.Parameters.AddWithValue("@UpdateBy", item.UpdateBy);

            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public void Delete(int id)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "[News_Delete]";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@Id", id);

            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public List<News> Select(NewsSearchCriteria item)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "[News_Select]";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@Id", item.Id);
            command.Parameters.AddWithValue("@Title", item.Title);
            command.Parameters.AddWithValue("@ShortDescription", item.ShortDescription);
            command.Parameters.AddWithValue("@Description", item.Description);
            command.Parameters.AddWithValue("@Date", item.Date);
            command.Parameters.AddWithValue("@CreationDate", item.CreationDate);
            command.Parameters.AddWithValue("@CreatedBy", item.CreatedBy);
            command.Parameters.AddWithValue("@UpdateDate", item.UpdateDate);
            command.Parameters.AddWithValue("@UpdateBy", item.UpdateBy);

            connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            List<News> lst = new List<News>();
            while (reader.Read())
            {
                News obj = new News();

                obj.Id = reader.GetInt32(0);
                obj.Title = reader.GetString(1);
                obj.ShortDescription = reader.GetString(2);
                obj.Description = reader.GetString(3);
                obj.Date = reader.GetDateTime(4);
                obj.ImageUrl = reader.GetString(5);
                obj.CreationDate = reader.GetDateTime(6);
                obj.CreatedBy = reader.GetInt32(7);
                if (!reader.IsDBNull(8))
                    obj.UpdateDate = reader.GetDateTime(8);
                if (!reader.IsDBNull(9))
                    obj.UpdateBy = reader.GetInt32(9);

                lst.Add(obj);
            }
            connection.Close();

            return lst;
        }

        public List<News> TopFive()
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "[TopFive]";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            List<News> lst = new List<News>();
            while (reader.Read())
            {
                News obj = new News();

                obj.Id = reader.GetInt32(0);
                obj.Title = reader.GetString(1);
                obj.ShortDescription = reader.GetString(2);
                obj.Description = reader.GetString(3);
                obj.Date = reader.GetDateTime(4);
                obj.ImageUrl = reader.GetString(5);
                obj.CreationDate = reader.GetDateTime(6);
                obj.CreatedBy = reader.GetInt32(7);
                if (!reader.IsDBNull(8))
                    obj.UpdateDate = reader.GetDateTime(8);
                if (!reader.IsDBNull(9))
                    obj.UpdateBy = reader.GetInt32(9);

                lst.Add(obj);
            }
            connection.Close();

            return lst;
        }
        //protected override void BuildInsertCommand(SqlCommand command, News news)
        //{
        //    command.CommandText = "INSERT INTO [NEWS]([DESCRIPTION]) VALUES(@Description)";
        //    command.Parameters.AddWithValue("@Description", news.Description);
        //}

        //public void Update(News item)
        //{
        //    SqlConnection connection = new SqlConnection();
        //    connection.ConnectionString = this.ConnectionString;

        //    SqlCommand command = new SqlCommand();
        //    command.CommandText = "UPDATE [NEWS] SET [DESCRIPTION] = @contentText WHERE [ID] = @id";
        //    command.Parameters.AddWithValue("@Title", item.Title);
        //    command.Parameters.AddWithValue("@Description", item.Description);
        //    command.Parameters.AddWithValue("@id", item.Id);
        //    command.Connection = connection;

        //    connection.Open();
        //    command.ExecuteNonQuery();
        //    connection.Close();
        //}

        //public void Delete(int id)
        //{
        //    SqlConnection connection = new SqlConnection();
        //    connection.ConnectionString = this.ConnectionString;

        //    SqlCommand command = new SqlCommand();
        //    command.CommandText = "DELETE [NEWS] WHERE [ID] = @id";
        //    command.Parameters.AddWithValue("@id", id);
        //    command.Connection = connection;

        //    connection.Open();
        //    command.ExecuteNonQuery();
        //    connection.Close();
        //}

        //public List<News> SelectAll()
        //{
        //    List<News> lst = new List<News>();

        //    SqlConnection connection = new SqlConnection();
        //    connection.ConnectionString = this.ConnectionString;

        //    SqlCommand command = new SqlCommand();
        //    command.CommandText = "SELECT * FROM [NEWS]";

        //    command.Connection = connection;

        //    connection.Open();

        //    SqlDataReader reader = command.ExecuteReader();
        //    while (reader.Read())
        //    {
        //        News obj = new News();
        //        obj.Id = reader.GetInt32(0);
        //        obj.Title = reader.GetString(1);
        //        lst.Add(obj);
        //    }

        //    connection.Close();

        //    return lst;
        //}

        //public List<News> SelectById(int id)
        //{
        //    List<News> lst = new List<News>();

        //    SqlConnection connection = new SqlConnection();
        //    connection.ConnectionString = this.ConnectionString;

        //    SqlCommand command = new SqlCommand();
        //    command.CommandText = "SELECT * FROM [NEWS] WHERE [ID] = @id";
        //    command.Parameters.AddWithValue("@id", id);
        //    command.Connection = connection;

        //    connection.Open();

        //    SqlDataReader reader = command.ExecuteReader();
        //    while (reader.Read())
        //    {
        //        News obj = new News();
        //        obj.Id = reader.GetInt32(0);
        //        obj.Title = reader.GetString(1);
        //        lst.Add(obj);
        //    }

        //    connection.Close();

        //    return lst;
        //}
    }
}
